package com.ssyt.hiringtwo.servies.pub;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ssyt.hiringtwo.common.DBUtil;
import com.ssyt.hiringtwo.common.StringUtil;
import com.ssyt.hiringtwo.entity.CompanyEntity;
import com.ssyt.hiringtwo.entity.PageModel;
import com.ssyt.hiringtwo.entity.PositionEntity;
import com.ssyt.hiringtwo.entity.TradeEntity;
import com.ssyt.hiringtwo.entity.TradePositionEntity;
import com.ssyt.hiringtwo.entity.TradeTechEntity;
import com.ssyt.hiringtwo.entity.UserEntity;
import com.ssyt.hiringtwo.entity.dtoTrade.Trade;
import com.ssyt.hiringtwo.servies.admin.TradeService;

public class UserService {
	TradeService trade = new TradeService();

	/**
	 * 登录
	 */
	public UserEntity login(UserEntity user) {
		String sql = "select * from user where email=? and password=?";
		return DBUtil.getUniqueResult(UserEntity.class, sql, user.getEmail(),
				user.getPassword());
	}

	/**
	 * 注册账号
	 * 
	 * @param user
	 * @return
	 */
	public int registeredSuccessService(UserEntity user) {
		String sql = "insert into user values(null,?,?,?,?,?,?)";
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(user.getEmail());
		paramList.add(user.getPassword());
		paramList.add(user.getUsername());
		paramList.add(user.getPhone());
		paramList.add(user.getRoleType());
		paramList.add(user.getIsActive());
		System.out.println(user.getEmail());
	  int row = DBUtil.executeUpdate(sql, paramList);
	  if (row!=0) {
		  System.out.println("成功！");
	  }else{
			System.out.println("失败！");
		}
	return row;
	}
	/**
	 * 修改状态激活
	 * @param user
	 * @return
	 */
	public  void  isactivation(UserEntity user) {
		String sql = "update user set is_active = ? where email=?";
		 DBUtil.executeUpdate(sql, user.getIsActive(),user.getEmail());
	}
	
	
	/**
	 * 查询最新职位信息 
	 * @return
	 */
	public List<PositionEntity> latestPositionListService() {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT p.job, p.id, p.name, p.publish_time,p.salary_begin,p.salary_end,p.work_experience,p.education,p.treatment,co.name AS 'company$name',t.name AS 'trade$name',co.nature AS 'company$nature',co.purpose AS 'company$purpose', ci.name AS 'city$name', pro.name AS 'province$name' ");
		sb.append("FROM position p, user u, company co, trade t, city ci, province pro ");
		sb.append("WHERE p.user_id=u.id AND co.user_id=u.id AND co.trade_id=t.id AND p.city_code=ci.code AND p.province_code=pro.code and now()<DATE_ADD(p.publish_time, INTERVAL p.valid_days DAY) ");
		sb.append("ORDER BY p.edit_time DESC ");
		sb.append("LIMIT 5");
		return DBUtil.executeQuery(PositionEntity.class, sb.toString());
	}
	/**
	 * 查询最新职位信息 +(条件查询)
	 * @return
	 */
	public List<PositionEntity> latestPositionListServicequery(PositionEntity positionEntity) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT p.job, p.id, p.name, p.publish_time,p.salary_begin,p.salary_end,p.work_experience,p.education,p.treatment,co.name AS 'company$name',t.name AS 'trade$name',co.nature AS 'company$nature',co.purpose AS 'company$purpose', ci.name AS 'city$name', pro.name AS 'province$name' ");
		sb.append("FROM position p, user u, company co, trade t, city ci, province pro ");
		sb.append("WHERE p.user_id=u.id AND co.user_id=u.id AND co.trade_id=t.id AND p.city_code=ci.code AND p.province_code=pro.code and now()<DATE_ADD(p.publish_time, INTERVAL p.valid_days DAY) ");
		sb.append(" and p.name like ? ");
		sb.append("ORDER BY p.edit_time DESC ");
		return DBUtil.executeQuery(PositionEntity.class, sb.toString(),"%"+positionEntity.getName()+"%");
	}
	
	
	
	
	
	
	/**
	 * 职位管理界面的最新职位查询  + 分页
	 * @param name 
	 * @param name 
	 */
	public  PageModel<PositionEntity> list(PositionEntity positionEntity, int pageSize,int pageNo,String name){
		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT p.job, p.id, p.name, p.publish_time,p.salary_begin,p.salary_end,p.work_experience,p.education,p.treatment,co.name AS 'company$name',t.name AS 'trade$name',co.nature AS 'company$nature',co.purpose AS 'company$purpose', ci.name AS 'city$name', pro.name AS 'province$name' ");
		sb.append("FROM position p, user u, company co, trade t, city ci, province pro ");
		sb.append("WHERE p.user_id=u.id AND co.user_id=u.id AND co.trade_id=t.id AND p.city_code=ci.code AND p.province_code=pro.code and now()<DATE_ADD(p.publish_time, INTERVAL p.valid_days DAY) ");
		if (StringUtil.isNotBlank(positionEntity.getName())) {
			sb.append(" and p.name like ? ");
			paramList.add("%"+positionEntity.getName()+"%");
		}
		
		sb.append("ORDER BY p.edit_time DESC ");
		sb.append(" limit ?,? ");
		
		paramList.add((pageNo-1)*pageSize);
		paramList.add(pageSize);
		
		return DBUtil.executeQueryByPage(PositionEntity.class, pageSize, pageNo, sb.toString(), paramList);
	}
	
	

	/**
	 * 通过userId查看是否有相关的公司信息记录
	 * 
	 */
	public CompanyEntity getCompanInfo(int userId) {
		String sql = "SELECT * FROM company WHERE user_id=?";
		return DBUtil.getUniqueResult(CompanyEntity.class, sql, userId);
	}

	/**
	 * 查询首页上的所有行业信息
	 * 
	 * @return
	 */
	public List<Trade> ListTreade() {
		List<Trade> tradeList = new ArrayList<Trade>();
		List<TradeEntity> listTrade = trade.listTrade();
		for (TradeEntity tradeEntity : listTrade) {
			String tradename = tradeEntity.getName();
			Trade trade = new Trade();
			trade.setTradeName(tradename);// 热门行业

			// 根据行业id查询行业技术(多个技术)
			int tradeid = tradeEntity.getId();
			String sql = "SELECT * from trade_tech where trade_id = ?";
			List<TradeTechEntity> tradeTechEntities = DBUtil.executeQuery(
					TradeTechEntity.class, sql, tradeid);
			Map<String, List<TradePositionEntity>> map = new HashMap<>();
			for (TradeTechEntity tradeTechEntity : tradeTechEntities) {
				String tradetechName = tradeTechEntity.getName();
				// 循环得到行业技术下面的相关职位
				Integer tradetech = tradeTechEntity.getId();
				String sql2 = "SELECT *  from trade_position WHERE trade_tech_id= ?";
				List<TradePositionEntity> TradePositionEntity = DBUtil
						.executeQuery(TradePositionEntity.class, sql2,
								tradetech);
				map.put(tradetechName, TradePositionEntity);

			}
			trade.setTradeTechAndPositionMap(map);
			// 设置热门职业hot
			String sql3 = "SELECT tra.`name` from trade t , trade_tech tr, trade_position tra  WHERE t.id = tr.trade_id AND tr.id = tra.trade_tech_id  and tra.is_hot=1 AND t.id=?";
			List<TradePositionEntity> TradePositionEntityHot = DBUtil
					.executeQuery(TradePositionEntity.class, sql3, tradeid);
			trade.setHotTreadPositionList(TradePositionEntityHot);
			tradeList.add(trade);
		}
		return tradeList;
	}

	/**
	 *  查询职位详情
	 * @param id
	 * @return
	 */
	public PositionEntity positionDetails(int id) {
		String sql = "SELECT p.*, pr.name AS 'province$name', ci.name AS 'city$name', c.logo AS 'company$logo', c.`name` AS 'company$name', t.`name` AS 'trade$name', c.nature AS 'company$nature', c.size AS 'company$size', c.linkman AS 'company$linkman', c.phone AS 'company$phone', c.address AS 'company$address'  FROM position p, company c, user u, city ci, province pr, trade t WHERE p.user_id=u.id AND c.user_id=u.id AND p.city_code=ci.code AND p.province_code=pr.`code` AND c.trade_id=t.id AND p.id=?";
		return DBUtil.getUniqueResult(PositionEntity.class, sql, id);
	}

	

	/**
	 * 查询相似职位
	 * 
	 * @param name
	 * @param id
	 * @return
	 */
	public List<PositionEntity> SimilarPosition(String name, int id) {
		  String sql = "SELECT p.id, p.name, p.salary_begin, p.salary_end, c.logo AS 'company$logo', c.name AS 'company$name' FROM position p, company c, user u WHERE p.user_id=u.id AND c.user_id=u.id AND p.name LIKE ? AND p.id!=? LIMIT 10";
	      return DBUtil.executeQuery(PositionEntity.class, sql, "%" + name + "%",id);
	}
	/**
	 * 修改密码
	 * @param id
	 * @param newpassword
	 */
	public int updatePassword(int id, String newpasswordH) {
		String sql ="update user set password = ? where id = ?";
		 int row = DBUtil.executeUpdate(sql, newpasswordH , id);
		if (row>0) {
			System.out.println("测试--修改成功");
		}else{
			System.out.println("测试--修改失败");
		}
		return row;
	}
	/**
	 * 通过邮箱修改密码
	 */
	public int updateEmailPassword(String email,String newpassword){
		String sql = "update user set password=? where email = ? ";
	  int row =  DBUtil.executeUpdate(sql,newpassword,email);
		if (row>0) {
			System.out.println("测试--修改成功");
		}else{
			System.out.println("测试--修改失败");
		}
		return row;
	}
	/**
	 * 根据输入邮箱账号修改密码(有bug没怎么做好)
	 * @param email
	 * @return
	 */
	public boolean isExistEmail(String email){
		boolean result = false;
		String sql = "select * from user where email = ?";
		UserEntity user = DBUtil.getUniqueResult(UserEntity.class, sql, email);
		if (user != null) {
			return true;
		}
		return result;
	}
}
